package com.hsnn.medstgmini.pro.plan.dao.impl;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.plan.dao.WsjPriceAdjustDao;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

/**
 * @category 卫生局调价管理
 * @author wangbing
 * @date 2015-11-12 17:34:40
 */
@Repository
public class WsjPriceAdjustDaoImpl extends BaseHibernateDAO implements WsjPriceAdjustDao{
	
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * @category 验证调价计划名称是否重复
	 * @auth wangbing
	 * @return true重复  flase不重复
	 * @date 2015-11-13 09:10:03
	 */
	@Override
	public boolean validateRepeat(String name,String id){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append(" select count(*) from base_priceadjplan where price_adj_plan_name = :name and price_adj_plan_id <> :id ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("name", name);
		query.setString("id", id);
		int count = Integer.parseInt(query.uniqueResult().toString());
		if(count > 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * @category 新增调价计划
	 * @auth wangbing
	 * @return 
	 * @date 2015-11-13 09:59:19
	 */
	@Override
	public void addPriceAdjPlan(Pagination page){
		String priceAdjPlanName = page.getConditions().get("priceAdjPlanName");
		String executeCat = page.getConditions().get("executeCat");
		String executeDatetime = page.getConditions().get("executeDatetime");
		String remark = page.getConditions().get("remark");
		String priceAdjAccord = page.getConditions().get("priceAdjAccord");
		String orgId =page.getConditions().get("orgId");
		String userId = page.getConditions().get("userId");
		String uuid = page.getConditions().get("uuid");
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append(" insert into base_priceadjplan values (:uuid,:priceAdjPlanName,null,:executeCat,")
		.append(":createDatetime,:userId,:orgId,")
		.append(executeDatetime==null?"null":":executeDatetime")
		.append(",:priceAdjAccord,:remark,0,:updDatetime,:userId)");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("uuid", uuid);
		query.setString("priceAdjPlanName", priceAdjPlanName);
		query.setString("executeCat", executeCat);
		query.setString("createDatetime", ParseDate.parseFullFormat(new Date()));
		query.setString("orgId", orgId);
		if(executeDatetime != null){
			query.setString("executeDatetime", executeDatetime);
		}
		query.setString("priceAdjAccord", priceAdjAccord);
		query.setString("remark", remark);
		query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
		query.setString("userId", userId);
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 * @category 获取未处理调价计划列表
	 * @auth wangbing
	 * @return
	 * @date 2015-11-13 08:32:31
	 */
	@Override
	public Pagination getNoPriceAdjPlanList(Pagination page){
		String priceAdjPlanName = page.getConditions().get("priceAdjPlanName");
		String executeCat = page.getConditions().get("executeCat");
		String createStartTime = page.getConditions().get("createStartTime");
		String createEndTime = page.getConditions().get("createEndTime");
		String executeStartTime = page.getConditions().get("executeStartTime");
		String executeEndTime = page.getConditions().get("executeEndTime");
		String orgId = page.getConditions().get("orgId");
		StringBuffer sql = new StringBuffer()
		.append("	select a.price_adj_plan_id priceAdjPlanId,a.price_adj_plan_name priceAdjPlanName,a.price_adj_plan_cat priceAdjPlanCat,")
		.append("	a.execute_cat executeCat,a.create_datetime createDatetime,")
		.append("	a.execute_datetime executeDatetime,a.price_adj_accord priceAdjAccord,")
		.append("	a.remark remark,a.status status,a.upd_datetime updDatetime ")
		.append("	from base_priceadjplan a  ")
		.append("	where a.status = 0 and a.create_org_id = :orgId ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("orgId", orgId);
		if(StringUtils.isNotBlank(createStartTime)){
			sql.append(" and a.create_datetime >= :createStartTime ");
			params.put("createStartTime", createStartTime + " 00:00:00");
		}
		if(StringUtils.isNotBlank(createEndTime)){
			sql.append(" and a.create_datetime <= :createEndTime ");
			params.put("createEndTime", createEndTime + " 23:59:59");
		}
		if(StringUtils.isNotBlank(executeStartTime)){
			sql.append(" and a.execute_datetime >= :executeStartTime ");
			params.put("executeStartTime", executeStartTime + " 00:00:00");
		}
		if(StringUtils.isNotBlank(executeEndTime)){
			sql.append(" and a.execute_datetime <= :executeEndTime ");
			params.put("executeEndTime", executeEndTime + " 23:59:59");
		}
		if(StringUtils.isNotBlank(executeCat)){
			sql.append(" and a.execute_cat = :executeCat ");
			params.put("executeCat", executeCat);
		}
		if(StringUtils.isNotBlank(priceAdjPlanName)){
			sql.append(" and a.price_adj_plan_name like :priceAdjPlanName ");
			params.put("priceAdjPlanName", "%"+priceAdjPlanName.trim()+"%");
		}
		sql.append(" order by a.execute_datetime desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 根据id获取调价计划
	 * @auth wangbing
	 * @return
	 * @date 2015-11-13 08:32:31
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Map<String,Object> getPriceAdjPlanById(String id){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append("	select a.price_adj_plan_id priceAdjPlanId,a.price_adj_plan_name priceAdjPlanName,a.price_adj_plan_cat priceAdjPlanCat,")
		.append("	a.execute_cat executeCat,a.create_datetime createDatetime,")
		.append("	a.execute_datetime executeDatetime,a.price_adj_accord priceAdjAccord,")
		.append("	a.remark remark,a.status status,a.upd_datetime updDatetime ")
		.append("	from base_priceadjplan a  ")
		.append("	where a.price_adj_plan_id = :id ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("id", id);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return (Map<String, Object>) query.uniqueResult();
	}
	
	/**
	 * @category 编辑调价计划
	 * @auth wangbing
	 * @return 
	 * @date 2015-11-13 09:59:19
	 */
	@Override
	public void editPriceAdjPlan(Pagination page){
		String priceAdjPlanName = page.getConditions().get("priceAdjPlanName");
		String executeCat = page.getConditions().get("executeCat");
		String executeDatetime = page.getConditions().get("executeDatetime");
		String remark = page.getConditions().get("remark");
		String priceAdjAccord = page.getConditions().get("priceAdjAccord");
		String userId = page.getConditions().get("userId");
		String priceAdjPlanId = page.getConditions().get("priceAdjPlanId");
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append(" update base_priceadjplan set price_adj_plan_name = :priceAdjPlanName,execute_cat = :executeCat,execute_datetime = ")
		.append(executeDatetime==null?"null":":executeDatetime")
		.append(",price_adj_accord = :priceAdjAccord,remark = :remark,upd_datetime = :updDatetime,upd_user = :userId where price_adj_plan_id = :priceAdjPlanId");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("priceAdjPlanId", priceAdjPlanId);
		query.setString("priceAdjPlanName", priceAdjPlanName);
		query.setString("executeCat", executeCat);
		if(executeDatetime != null){
			query.setString("executeDatetime", executeDatetime);
		}
		query.setString("priceAdjAccord", priceAdjAccord);
		query.setString("remark", remark);
		query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
		query.setString("userId", userId);
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 * 
	 * @category 获取未处理调价计划明细列表
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@Override
	public Pagination getPriceAdjPlanDetailList(Pagination page) {
		String prodComp = page.getConditions().get("prodComp");
		String projId = page.getConditions().get("projId");
		String goodsCode = page.getConditions().get("goodsCode");
		String drugName = page.getConditions().get("drugName");
		String drugCode = page.getConditions().get("drugCode");
		String priceAdjPlanId = page.getConditions().get("priceAdjPlanId");
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.price_adj_paln_detail_id priceAdjPalnDetailId,a.price_adj_plan_id priceAdjPlanId,")
		.append("	b.proj_name name,b.drug_code drugCode,b.qualityspec_name drugName,b.form_name formName,")
		.append("	b.factor factor,b.unit_name unit,b.prod_compname compName,b.material_name materialName,b.prod_compcode prodCompId,")
		.append("	a.orig_pro_price_limit proPriceLimit,a.curr_pro_price_limit currProPriceLimit,b.drug_code drugId,")
		.append("	a.orig_retail_price_limit retailPriceLimit,a.curr_retail_price_llimit currRetailPriceLlimit,")
		.append("	a.goods_code goodsCode,b.spec_name specName")
		.append("	FROM base_goods_item  b LEFT JOIN base_priceadjplandetail a ON b.goods_code = a.goods_code ")
		.append("	WHERE a.price_adj_plan_id = :priceAdjPlanId ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("priceAdjPlanId", priceAdjPlanId);
		if(StringUtils.isNotBlank(prodComp)){
			sql.append(" and (b.prod_compname like :prodComp or b.prod_compname_spel like :prodComp)");
			params.put("prodComp", "%"+prodComp+"%");
		}
		if(StringUtils.isNotBlank(projId)){
			sql.append(" and b.proj_id = :projId ");
			params.put("projId", projId);
		}
		if(StringUtils.isNotBlank(goodsCode)){
			sql.append(" and a.goods_code = :goodsCode ");
			params.put("goodsCode", goodsCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 * @category 获取未加入调价计划商品列表
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Pagination getPriceAdjPlanDurgList(Pagination page) {
		Session session = super.getSession();
		String drugComp = page.getConditions().get("prodComp");
		String projName = page.getConditions().get("projName");
		String goodsCode = page.getConditions().get("goodsCode");
		String drugName = page.getConditions().get("drugName");
		String drugCode = page.getConditions().get("drugCode");
		String priceid = page.getConditions().get("priceid");
		String orgId = page.getConditions().get("orgId");
		String areaId = getAreaIdByOrgId(orgId);
		String areaPrefix = AreaUtil.getAreaPrefix(areaId);
		String[] superArea = AreaUtil.getSuperArea(areaId);
		
		
		StringBuffer sql = new StringBuffer()
		.append("	SELECT ")
		.append("	b.proj_name projName,b.drug_code drugCode,b.qualityspec_name drugName,b.form_name formName,")
		.append("	b.factor factor,b.unit_name unit,b.prod_compname prodComp,b.material_name materialName,b.prod_compcode prodCompId,")
		.append("	b.pro_price_limit proPriceLimit,b.drug_code drugId,")
		.append("	b.retail_price_limit retailPriceLimit,")
		.append("	b.goods_code goodsCode,b.spec_name specName")
		.append("	FROM base_goods_item  b  ")
		.append("   where b.proj_starttime < :currentTime and b.proj_endtime > :currentTime and b.status = 1 ")
		.append("   and b.goods_code not in (select a.goods_code from base_priceadjplandetail a ")
		.append("   where a.price_adj_plan_id = :priceid) and ( b.area_id like :areaPrefix ");
		
		StringBuffer goodsCountSql =  new StringBuffer()//商品总数
		.append(" select count(*) from base_goods_item  b where b.proj_starttime < :currentTime  ")
		.append(" and b.proj_endtime > :currentTime and b.status = 1 and ( b.area_id like :areaPrefix ");
		
		if(superArea != null){
			for(int i = 0;i<superArea.length;i++){
				sql.append(" or b.area_id = :superArea").append(i+"");
				goodsCountSql.append(" or b.area_id = :superArea").append(i+"");
			}
		}
		sql.append(")");
		goodsCountSql.append(")");
		StringBuffer detailCountSql = new StringBuffer()//调价计划明细数
		.append(" select count(*) from base_goods_item  b left join base_priceadjplandetail a on a.goods_code = b.goods_code")
		.append(" where a.price_adj_plan_id = :priceid ");
		
		if(StringUtils.isNotBlank(drugComp)){
			sql.append(" and (b.prod_compname like :drugComp or b.prod_compname_spel like :drugComp)");
			goodsCountSql.append(" and (b.prod_compname like :drugComp or b.prod_compname_spel like :drugComp)");
			detailCountSql.append(" and (b.prod_compname like :drugComp or b.prod_compname_spel like :drugComp)");
		}
		if(StringUtils.isNotBlank(projName)){
			sql.append(" and b.proj_name like :projName ");
			goodsCountSql.append(" and b.proj_name like :projName ");
			detailCountSql.append(" and b.proj_name like :projName ");
		}
		if(StringUtils.isNotBlank(goodsCode)){
			sql.append(" and b.goods_code = :goodsCode ");
			goodsCountSql.append(" and b.goods_code = :goodsCode ");
			detailCountSql.append(" and b.goods_code = :goodsCode ");
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			goodsCountSql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			detailCountSql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
		}
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			goodsCountSql.append(" and b.drug_code = :drugCode ");
			detailCountSql.append(" and b.drug_code = :drugCode ");
		}
		
		Query query = session.createSQLQuery(sql.toString());
		Query goodsQuery = session.createSQLQuery(goodsCountSql.toString());
		Query detailQuery = session.createSQLQuery(detailCountSql.toString());
		query.setString("priceid", priceid);
		query.setString("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		detailQuery.setString("priceid", priceid);
		query.setString("areaPrefix", "%"+areaPrefix+"%");
		goodsQuery.setString("areaPrefix", "%"+areaPrefix+"%");
		goodsQuery.setString("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		if(superArea != null){
			for(int i = 0;i<superArea.length;i++){
				query.setString("superArea"+i, superArea[i]);
				goodsQuery.setString("superArea"+i, superArea[i]);
			}
		}
		if(StringUtils.isNotBlank(drugComp)){
			query.setString("drugComp", "%"+drugComp.trim()+"%");
			goodsQuery.setString("drugComp", "%"+drugComp.trim()+"%");
			detailQuery.setString("drugComp", "%"+drugComp.trim()+"%");
		}
		if(StringUtils.isNotBlank(projName)){
			query.setString("projName", "%"+projName.trim()+"%");
			goodsQuery.setString("projName", "%"+projName.trim()+"%");
			detailQuery.setString("projName", "%"+projName.trim()+"%");
		}
		if(StringUtils.isNotBlank(goodsCode)){
			query.setString("goodsCode", goodsCode.trim());
			goodsQuery.setString("goodsCode", goodsCode.trim());
			detailQuery.setString("goodsCode", goodsCode.trim());
		}
		if(StringUtils.isNotBlank(drugName)){
			query.setString("drugName", "%"+drugName.trim()+"%");
			goodsQuery.setString("drugName", "%"+drugName.trim()+"%");
			detailQuery.setString("drugName", "%"+drugName.trim()+"%");
		}
		if(StringUtils.isNotBlank(drugCode)){
			query.setString("drugCode", drugCode.trim());
			goodsQuery.setString("drugCode", drugCode.trim());
			detailQuery.setString("drugCode", drugCode.trim());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.list();
		page.setRows(list);
		
		int goodsCount = Integer.parseInt(goodsQuery.uniqueResult().toString());
		int detailCount = Integer.parseInt(detailQuery.uniqueResult().toString());
		int records = goodsCount - detailCount;
		page.setRecords(records);
		return page;
	}
	
	/**
	 * 
	 * @category 加入调价计划
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@Override
	public int addDetail(List<Map<String,String>> list){
		int sum = 0;
		for(Map<String,String> map:list){
			int count = addOneDetail(map);
			sum += count;
		}
		return sum;
	}
	
	/**
	 * 
	 * @category 加入一个调价计划
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	private int addOneDetail(Map<String,String> map){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into base_priceadjplandetail values (?,?,?,?,?,?,?,?,?)");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter(0, map.get("priceAdjPalnDetailId"));
		query.setParameter(1, map.get("priceAdjPlanId"));
		query.setParameter(2, map.get("goodsCode"));
		query.setParameter(3, map.get("origProPriceLimit"));
		query.setParameter(4, map.get("currProPriceLimit"));
		query.setParameter(5, map.get("origRetailPriceLimit"));
		query.setParameter(6, map.get("currRetailPriceLlimit"));
		query.setParameter(7, map.get("addDatetime"));
		query.setParameter(8, map.get("addUser"));
		int count = query.executeUpdate();
		session.flush();
		return count;
	}
	
	/**
	 * 
	 * @category 删除调价计划明细
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@Override
	public int deleteDetail(List<String> list){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append("delete from base_priceadjplandetail where price_adj_paln_detail_id in (:list)");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameterList("list", list);
		int sum = query.executeUpdate();
		session.flush();
		return sum;
	}
	
	/**
	 * 
	 * @category 保存调价计划明细
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@Override
	public int saveDetail(List<Map<String,String>> list){
		int sum = 0;
		for(Map<String,String> map:list){
			int count = saveOneDetail(map);
			sum += count;
		}
		return sum;
	}
	
	/**
	 * 
	 * @category 保存一个调价计划明细
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	private int saveOneDetail(Map<String,String> map){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append(" update base_priceadjplandetail set curr_pro_price_limit = :currProPriceLimit,")
		.append(" curr_retail_price_llimit = :currRetailPriceLlimit where price_adj_paln_detail_id = :priceAdjPalnDetailId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("currProPriceLimit", map.get("currProPriceLimit"));
		query.setString("currRetailPriceLlimit", map.get("currRetailPriceLlimit"));
		query.setString("priceAdjPalnDetailId", map.get("priceAdjPalnDetailId"));
		int count = query.executeUpdate();
		return count;
	}
	
	/**
	 * @category 执行调价计划
	 * @auth wangbing
	 * @date 2015年6月24日
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void runPriceAdjPlan(Pagination page) {
		String planId = page.getConditions().get("planId");
		String userId = page.getConditions().get("userId");
		String updDatetime = DateUtil.dateTime(new Timestamp(System.currentTimeMillis()));
		Session session = getSession();
		if (StringUtils.isNotBlank(planId)) {
			// 查询计划下明细列表
			StringBuffer detailSql = new StringBuffer()
			.append(" select goods_code goodsCode,curr_pro_price_limit currProPriceLimit,curr_retail_price_llimit currRetailPriceLlimit ")
			.append(" from base_priceadjplandetail where price_adj_plan_id = :priceAdjPlanId ");
			Map<String,Object> detailParams = new HashMap<String,Object>();
			detailParams.put("priceAdjPlanId", planId);
			List<Map<String,Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(detailSql.toString(), detailParams);
			if (list != null && !list.isEmpty()) {
				for (Map<String,Object> detail : list) {
					// 最高采购限价
					// 更新商品表价格
					StringBuffer sb = new StringBuffer();
					sb.append("update base_goods_item ");
					sb.append("set pro_price_limit=:proPriceLimit, ");
					sb.append(" retail_price_limit=:retailPriceLimit, ");
					sb.append(" upd_datetime=:updDatetime, ");
					sb.append(" upd_user=:updUser ");
					sb.append("where goods_code=:goodsCode and pro_price_limit > :proPriceLimit and retail_price_limit > :retailPriceLimit ");
					SQLQuery query = session.createSQLQuery(sb.toString());
					query.setBigDecimal("proPriceLimit", new BigDecimal(detail.get("currProPriceLimit").toString()));
					query.setBigDecimal("retailPriceLimit",new BigDecimal(detail.get("currRetailPriceLlimit").toString()));
					query.setString("updDatetime", updDatetime);
					query.setString("updUser", userId);
					query.setString("goodsCode", detail.get("goodsCode").toString());
					query.executeUpdate();
					session.flush();
					// 插入商品表日志
					String sql = "INSERT INTO base_goods_item_log SELECT :logId log_id, a.*,null detail FROM base_goods_item a WHERE a.goods_code = :goodsCode";
					query = session.createSQLQuery(sql);
					query.setString("logId", UUID.randomUUID().toString());
					query.setString("goodsCode", detail.get("goodsCode").toString());
					query.executeUpdate();
					session.flush();

					// 更新购物计划(采购价不能超过最高采购限价)
					StringBuffer purPlanSql = new StringBuffer()
					.append("update pur_purplan set pro_price = :proPriceLimit,upd_datetime = :updDatetime,upd_user = :userId ")
					.append(" where pro_price > :proPriceLimit and goods_code = :goodsCode ");
					Query purPlanQuery = session.createSQLQuery(purPlanSql.toString());
					purPlanQuery.setBigDecimal("proPriceLimit", new BigDecimal(detail.get("currProPriceLimit").toString()));
					purPlanQuery.setString("updDatetime", updDatetime);
					purPlanQuery.setString("userId", userId);
					purPlanQuery.setString("goodsCode", detail.get("goodsCode").toString());
					purPlanQuery.executeUpdate();
					session.flush();
					

					// 更新医疗机构勾选的商品目录(采购价不能超过最高采购限价)
					StringBuffer getIdSql = new StringBuffer()
					.append("select hosp_pro_cat_id from pur_hospcatalog where pro_price > :proPriceLimit and goods_code = :goodsCode ");
					Query getIdQuery = session.createSQLQuery(getIdSql.toString());
					getIdQuery.setBigDecimal("proPriceLimit", new BigDecimal(detail.get("currProPriceLimit").toString()));
					getIdQuery.setString("goodsCode", detail.get("goodsCode").toString());
					List<String> idList = getIdQuery.list();
					
					StringBuffer hospSql = new StringBuffer()
					.append("update pur_hospcatalog set pro_price = :proPriceLimit,upd_datetime = :updDatetime,upd_user = :userId ")
					.append(" where pro_price > :proPriceLimit and goods_code = :goodsCode ");
					Query hospQuery = session.createSQLQuery(hospSql.toString());
					hospQuery.setBigDecimal("proPriceLimit", new BigDecimal(detail.get("currProPriceLimit").toString()));
					hospQuery.setString("updDatetime", updDatetime);
					hospQuery.setString("userId", userId);
					hospQuery.setString("goodsCode", detail.get("goodsCode").toString());
					hospQuery.executeUpdate();
					session.flush();
					
					//写日志
					for(String hospProCatId : idList){
						StringBuffer logSql = new StringBuffer()
						.append("INSERT INTO pur_hospcatalog_log SELECT :logId log_id, a.hosp_pro_cat_id, a.hosp_id, a.goods_code, ")
						.append("a.del_comp_id, a.pro_price, a.pro_count, a.status, a.bak_pur_count, 1 upd_type, a.upd_datetime, ")
						.append("a.upd_user FROM pur_hospcatalog a WHERE a.hosp_pro_cat_id = :hospProCatId ");
						Query logQuery = session.createSQLQuery(logSql.toString());
						logQuery.setString("logId", UUID.randomUUID().toString());
						logQuery.setString("hospProCatId", hospProCatId);
						logQuery.executeUpdate();
						session.flush();
					}
					
					
				}
			}

			// 更新计划表状态为已处理
			StringBuffer sb = new StringBuffer();
			sb.append("update base_priceadjplan set ");
			sb.append("status=1 ");
			sb.append(", upd_datetime=:updDatetime ");
			sb.append(", upd_user=:updUser ");
			sb.append(", execute_datetime=:executeDatetime ");
			sb.append("where price_adj_plan_id=:priceAdjPlanId ");
			SQLQuery query = session.createSQLQuery(sb.toString());
			query.setString("updDatetime", updDatetime);
			query.setString("updUser", userId);
			query.setString("executeDatetime", updDatetime);
			query.setString("priceAdjPlanId", planId);
			query.executeUpdate();
			session.flush();
		}
	}
	
	/**
	 * @category 根据机构id获取地区id
	 * @auth wangbing
	 * @date 2015年6月24日
	 */
	private String getAreaIdByOrgId(String orgId) {
		Session session = super.getSession();
	    String sql = "select area_id from std_healthbureau where hea_bur_id = :orgId ";
	    Query query = session.createSQLQuery(sql);
	    query.setString("orgId", orgId);
	    return query.uniqueResult().toString();
	}
	
	/**
	 * @category 获取已处理调价计划列表
	 * @auth wangbing
	 * @return
	 * @date 2015-11-13 08:32:31
	 */
	@Override
	public Pagination getPriceAdjPlanList(Pagination page){
		String priceAdjPlanName = page.getConditions().get("priceAdjPlanName");
		String executeCat = page.getConditions().get("executeCat");
		String createStartTime = page.getConditions().get("createStartTime");
		String createEndTime = page.getConditions().get("createEndTime");
		String executeStartTime = page.getConditions().get("executeStartTime");
		String executeEndTime = page.getConditions().get("executeEndTime");
		String orgId = page.getConditions().get("orgId");
		StringBuffer sql = new StringBuffer()
		.append("	select a.price_adj_plan_id priceAdjPlanId,a.price_adj_plan_name priceAdjPlanName,a.price_adj_plan_cat priceAdjPlanCat,")
		.append("	a.execute_cat executeCat,a.create_datetime createDatetime,")
		.append("	a.execute_datetime executeDatetime,a.price_adj_accord priceAdjAccord,")
		.append("	a.remark remark,a.status status,a.upd_datetime updDatetime ")
		.append("	from base_priceadjplan a  ")
		.append("	where a.status = 1 and a.create_org_id = :orgId ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("orgId", orgId);
		if(StringUtils.isNotBlank(createStartTime)){
			sql.append(" and a.create_datetime >= :createStartTime ");
			params.put("createStartTime", createStartTime + " 00:00:00");
		}
		if(StringUtils.isNotBlank(createEndTime)){
			sql.append(" and a.create_datetime) <= :createEndTime ");
			params.put("createEndTime", createEndTime + " 23:59:59");
		}
		if(StringUtils.isNotBlank(executeStartTime)){
			sql.append(" and a.execute_datetime >= :executeStartTime ");
			params.put("executeStartTime", executeStartTime + " 00:00:00");
		}
		if(StringUtils.isNotBlank(executeEndTime)){
			sql.append(" and a.execute_datetime <= :executeEndTime ");
			params.put("executeEndTime", executeEndTime + " 23:59:59");
		}
		if(StringUtils.isNotBlank(executeCat)){
			sql.append(" and a.execute_cat = :executeCat ");
			params.put("executeCat", executeCat);
		}
		if(StringUtils.isNotBlank(priceAdjPlanName)){
			sql.append(" and a.price_adj_plan_name like :priceAdjPlanName ");
			params.put("priceAdjPlanName", "%"+priceAdjPlanName.trim()+"%");
		}
		sql.append(" order by a.execute_datetime desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 * @category 获取已处理调价计划明细列表
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@Override
	public Pagination getPriceDetailList(Pagination page) {
		String prodComp = page.getConditions().get("prodComp");
		String projId = page.getConditions().get("projId");
		String goodsCode = page.getConditions().get("goodsCode");
		String drugName = page.getConditions().get("drugName");
		String drugCode = page.getConditions().get("drugCode");
		String priceAdjPlanId = page.getConditions().get("priceAdjPlanId");
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.price_adj_paln_detail_id priceAdjPalnDetailId,a.price_adj_plan_id priceAdjPlanId,")
		.append("	b.proj_name name,b.drug_code drugCode,b.qualityspec_name drugName,b.form_name formName,")
		.append("	b.factor factor,b.unit_name unit,b.prod_compname compName,b.material_name materialName,b.prod_compcode prodCompId,")
		.append("	a.orig_pro_price_limit proPriceLimit,a.curr_pro_price_limit currProPriceLimit,b.drug_code drugId,")
		.append("	a.orig_retail_price_limit retailPriceLimit,a.curr_retail_price_llimit currRetailPriceLlimit,")
		.append("	a.goods_code goodsCode,b.spec_name specName")
		.append("	FROM base_goods_item  b LEFT JOIN base_priceadjplandetail a ON b.goods_code = a.goods_code ")
		.append("	WHERE a.price_adj_plan_id = :priceAdjPlanId ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("priceAdjPlanId", priceAdjPlanId);
		if(StringUtils.isNotBlank(prodComp)){
			sql.append(" and (b.prod_compname like :prodComp or b.prod_compname_spel like :prodComp)");
			params.put("prodComp", "%"+prodComp+"%");
		}
		if(StringUtils.isNotBlank(projId)){
			sql.append(" and b.proj_id = :projId ");
			params.put("projId", projId);
		}
		if(StringUtils.isNotBlank(goodsCode)){
			sql.append(" and a.goods_code = :goodsCode ");
			params.put("goodsCode", goodsCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		return dao.getPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 * @category 获取历史调价信息
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@Override
	public Pagination getGoodsHistoryPriceList(Pagination page) {
		String goodsCode = page.getConditions().get("goodsCode");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String prodComp = page.getConditions().get("prodComp");
		String projId = page.getConditions().get("projId");
		String endTime = page.getConditions().get("endTime");
		String startTime = page.getConditions().get("startTime");
		String orgId = page.getConditions().get("orgId");
		
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.price_adj_paln_detail_id priceAdjPalnDetailId,a.price_adj_plan_id priceAdjPlanId,t.execute_datetime executeDatetime,")
		.append("	b.proj_name projName,b.drug_code drugCode,b.qualityspec_name drugName,b.form_name formName,")
		.append("	b.factor factor,b.unit_name unit,b.prod_compname prodComp,b.material_name materialName,b.prod_compcode prodCompId,")
		.append("	a.orig_pro_price_limit origProPriceLimit,a.curr_pro_price_limit currProPriceLimit,b.drug_code drugId,")
		.append("	a.orig_retail_price_limit origRetailPriceLimit,a.curr_retail_price_llimit currRetailPriceLlimit,")
		.append("	a.add_datetime addDatetime,a.goods_code goodsCode,b.spec_name specName,t.price_adj_accord priceAdjAccord")
		.append("	FROM base_priceadjplan t left join base_priceadjplandetail a on a.price_adj_plan_id = t.price_adj_plan_id ")
		.append("   LEFT JOIN base_goods_item  b ON b.goods_code = a.goods_code")
		.append("	WHERE t.status = 1 and t.create_org_id = :orgId ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("orgId", orgId);
		if(StringUtils.isNotBlank(prodComp)){
			sql.append(" and (b.prod_compname like :prodComp or b.prod_compname_spel like :prodComp)");
			params.put("prodComp", "%"+prodComp.trim()+"%");
		}
		if(StringUtils.isNotBlank(projId)){
			sql.append(" and b.proj_id = :projId ");
			params.put("projId", projId);
		}
		if(StringUtils.isNotBlank(goodsCode)){
			sql.append(" and a.goods_code = :goodsCode ");
			params.put("goodsCode", goodsCode.trim());
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName.trim()+"%");
		}
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode.trim());
		}
		if(StringUtils.isNotBlank(startTime)){
			sql.append(" and t.execute_datetime >= :startTime ");
			params.put("startTime", startTime+" 00:00:00");
		}
		if(StringUtils.isNotBlank(endTime)){
			sql.append(" and t.execute_datetime <= :endTime ");
			params.put("endTime", endTime+" 23:59:59");
		}
		sql.append(" order by b.goods_code,t.execute_datetime desc ");
		return dao.getPageMapList(sql.toString(), params, page);
	}
}
